Back to Main Menu

ArcGIS-myData Data Integration

Introduction

This article outlines some concepts and methods for integrating an ArcGIS Enterprise geodatabase directly with and Assetic myData SQL Server database.

The integration provides the following:

  1. Update the ArcGIS Feature class database table with Assetic myData asset attributes. To do this a common identifier needs to exist between an asset in Assetic myData and a feature in the ArcGIS geodatabase
  2. Upon creation of a new feature in ArcGIS a new asset is created in Assetic myData. The generated Assetic Asset Id is applied to the new feature in ArcGIS, thus establishing a common identifier between the two databases.
  3. Upon deletion of feature in ArcGIS the corresponding asset in Assetic myData is updated to have a status of “Deleted by GIS”. If features in the GIS are never deleted but instead have an attribute that flags the feature as archived, then that can act as the trigger to set the status in Assetic myData.

 

Options for applying the integration to create assets include:

  • Using Database Triggers in the ArcGIS geodatabase table to initiate the asset creation in Assetic and update of asset status
    • This creates an asset in Assetic immediately
  • Using a SQL script to create new assets in Assetic on a periodic basis, i.e. nightly or weekly
    • This has the advantage of not requiring the ArcGIS Enterprise geodatabase feature class tables to be modified to support triggers

 

The process of updating the ArcGIS Enterprise geodatabase is typically performed as periodic (nightly/weekly) SQL script that updates the feature class tables in the ArcGIS Enterprise geodatabase table with asset attributes from the Assetic myData SQL Server database tables.

Requirements

The ArcGIS edit environment is using either:

  1. A non-versioned feature class, hence, there is no need to worry about versioning
  2. versioned feature class with the option to ‘return edits to base’ selected

 

The ArcGIS Enterprise geodatabase instance needs to have a database link to the Assetic SQL Server database.  The link needs to support read, create, and update SQL queries (DML queries).

myData Configuration

To support GIS Integration using Database Triggers/Scripts, linked asset categories in myData must have their Asset ID Mode set to Auto Generated.

 

Assetic also recommends switching off the ability to Create assets in myData to ensure that all assets originate from the GIS.

 

To verify these settings, please check the configuration in Tools > Category Configurations.

 

Trigger Based Integration

The creation of new asset features in ArcGIS-myData may have the following data integration architecture:

  1. The asset is first created in ArcGIS. A ‘on insert’ database trigger is created in the ArcGIS database tables for the asset feature classes.
  2. Upon insert the trigger creates a new asset in the Assetic myData database using SQL. This has the following implications:
    1. Appropriate database permissions are required to permit the creation of a new record in the Assetic myData database
    2. The GIS is using either:
      1. non-versioned editing, hence, there is no need to worry about versioning
      2. versioned feature class with the option to ‘return edits to base’ selected
    3. The Asset ID auto number feature is enabled in Assetic myData.
  3. The newly created asset ID is applied to an Assetic myData asset ID field in the GIS database table. This common ID is the basis of the link between the two systems.
  4. ArcGIS is able to retrieve asset details using the common asset ID as a key between the GIS table and Assetic myData table.
    1. Each Assetic asset category has a single database table that records all asset register information, the database query to extract the asset attributes is therefore straightforward.

 

The deletion of an asset feature follows a similar process:

  1. A ‘on delete’ database trigger is created in the ArcGIS database tables for asset feature classes.
  2. Upon deletion of a feature the trigger updates the ‘status’ field in the Assetic myData database using SQL. The asset is not disposed automatically.  A saved search/report will be run regularly to find all assets flagged for disposal, and the disposal is managed by the user using standard Assetic myData disposal functionality (bulk or per asset disposal options are available). This has the following implications:
    1. Appropriate database permissions are required to permit the creation of a updating of a record in the Assetic myData database
    2. The GIS is using either:
      1. non-versioned editing, hence, there is no need to worry about versioning
      2. versioned feature class with the option to ‘return edits to base’ selected

 

Updating a spatial feature in ArcGIS will typically have no impact on myData, since the asset attributes are managed in Assetic myData.

Sample Trigger:

The following is a sample of the After Insert trigger in the Enterprise Geodatabase table.  A trigger similar to this trigger is created in each asset feature class table.

CREATE TRIGGER [dbo].[BuildingsUpdateAssetIDonInsert]
   ON  [dbo].[BuildingsTrig]
   AFTER INSERT
AS
BEGIN
Declare @recordTimeStamp DateTime
Declare @insertedValuesCount numeric
Declare @uniqueId as numeric
Declare @insertedGISKey numeric
Declare @globalAssetID varchar(max)

Create table #temp ([OBJECTID_1] int)

insert into #temp ([OBJECTID_1]) (select [OBJECTID_1] from inserted)

Begin TRY
       Declare insertedGISEntity_Cur Cursor for select OBJECTID_1 from #temp
End TRY
Begin Catch
End Catch

set @insertedValuesCount = (select count(OBJECTID_1) from #temp)

if @insertedValuesCount > 0
       begin
              Open insertedGISEntity_Cur
              fetch next from insertedGISEntity_Cur into @insertedGISKey
              While @@Fetch_Status = 0
                      Begin
                             set @recordTimeStamp = GETDATE()
                             insert into [Consultant_Demo].[dbo].[Buildings] (_Timestamp) values (@recordTimeStamp)

                             set @uniqueId = SCOPE_IDENTITY()
                             set @globalAssetID = (select assetid from [Consultant_Demo].[dbo].[Buildings] where uniqueID = @uniqueId)
                             update BuildingsTrig SET ASSET_ID = @globalAssetID where OBJECTID_1 = @insertedGISKey
                             fetch next from insertedGISEntity_Cur INTO @insertedGISKey
                      End
                      Close insertedGISEntity_Cur
       End
delete #temp
DEALLOCATE insertedGISEntity_Cur
END

Scripted Integration

The process for applying the asset creation and asset status updates as an after hours script is quite similar to the trigger script.

  1. Create one of more new asset features in one or more ArcGIS feature classes
  2. Automatically execute an afterhours SQL script (via SQL Server task scheduling) to look for features without an Assetic myData asset ID (implies it is a new asset)
    1. Insert a new record into the Assetic myData database table. An asset ID is generated by the insert
    2. Update the GIS feature record with the generated Assetic myData asset Id

 

If features in the ArcGIS Enterprise geodatabase are deleted it is difficult to identify the deletion via script.  If instead the features are tagged with an archive flag then that flag may be used to update the Assetic myData asset status.